package com.cj.dao.impl;

import com.cj.dao.EmployeeDao;
import com.cj.entity.Employee;
import com.cj.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * Employee数据持久层EmployeeDao
 */
public class EmployeeDaoImpl extends BaseDaoImpl implements EmployeeDao {

    //查询员工信息列表
    @Override
    public List<Employee> empList() {
        List<Employee> empList = new ArrayList<>();
        String sql = "select * from emp order by id desc";
        ResultSet res = query(sql);
        try {
            if (res != null) {
                empList = new ArrayList<>();
                while (res.next()) {
                    //第一次进来，获取到的是第一条数据
                    //取得一行中的所有列的数据
//                res.getInt(0);
                    int id = res.getInt("id");
                    String workNo = res.getString("workno");
                    String name = res.getString("name");
                    String gender = res.getString(4); //第4列
                    int age = res.getInt(5);
                    String idCard = res.getString("idcard");
                    String workAddress = res.getString("workaddress");
                    Date date = res.getDate("entrydate");

                    //将获取到的数据映射到实体对象中
                    Employee emp = new Employee(id, workNo, name, gender, age, idCard, workAddress, date);
                    empList.add(emp);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return empList;
    }

    /**
     * 添加员工信息
     *
     * @param emp
     * @return
     */
    @Override
    public int addEmployee(Employee emp) {
        int result = 0; //返回的结果  1:执行成功   0：执行失败

        String sql = "insert into \n" +
                "    emp(workno,name,gender,age,idcard,workaddress,entrydate)\n" +
                " VALUE(?, ?, ?, ?, ?, ?, ?)";
        Object[] obj = {emp.getWorkNo(), emp.getName(), emp.getGender(), emp.getAge(),
                emp.getIdCard(), emp.getWorkAddress(), emp.getEntryDate()};
        result = executeUpdate(sql, obj);
        return result;
    }

    @Override
    public int updateEmployee(Employee emp) {
        int result = 0;
        String sql = "update emp \n" +
                "    set workno = ?, name = ?,gender = ?,age = ?,\n" +
                "        idcard = ?, workaddress = ?\n" +
                "    where id = ?";
        Object[] obj = {emp.getWorkNo(), emp.getName(), emp.getGender(), emp.getAge(),
                emp.getIdCard(), emp.getWorkAddress(), emp.getId()};
        result = executeUpdate(sql, obj);
        return result;
    }

    @Override
    public int deleteEmployee(int id) {
        int result = 0;
        String sql = "delete from emp where id = ?";
        Object[] obj = {id};
        //调用数据工具的执行sql方法
        result = executeUpdate(sql, obj);
        //将结果返回
        return result;
    }

    /**
     * 根据id查询员工实体对象
     *
     * @param id
     * @return
     */
    @Override
    public Employee getEmployeeById(int id) {
        Employee emp = null;
        String sql = "select * from emp where id = " + id;
        ResultSet res = null;
        try {
            res = query(sql);
            if (res != null) {
                if (res.next()) {
                    String workNo = res.getString("workno");
                    String name = res.getString("name");
                    String gender = res.getString(4); //第4列
                    int age = res.getInt(5);
                    String idCard = res.getString("idcard");
                    String workAddress = res.getString("workaddress");
                    Date date = res.getDate("entrydate");
                    //将获取到的数据映射到实体对象中
                    emp = new Employee(id, workNo, name, gender, age, idCard, workAddress, date);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return emp;
    }

    @Override
    public List<Employee> empListBypage(int begin, int limit) {
        DBUtils dbUtils = new DBUtils();
        List<Employee> empList = new ArrayList<>();
        String sql = "select * from emp limit ?,?";
        Connection conn = dbUtils.getConnection();
        PreparedStatement st = null;
        ResultSet rst = null;
        try {
            st = conn.prepareStatement(sql);
            st.setInt(1, begin);
            st.setInt(2, limit);
            //返回查询的结果集
            rst = st.executeQuery();
            while (rst.next()) {
                Employee emp = new Employee(rst.getInt("id"), rst.getString("workno"), rst.getString("name"), rst.getString(4), rst.getInt(5), rst.getString("idcard"), rst.getString("workaddress"), rst.getDate("entrydate"));
                empList.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            dbUtils.closeConn();
        }
        return empList;
    }


    public static void main(String[] args) {
        //测试添加方法，是否成功
        EmployeeDaoImpl employeeDao = new EmployeeDaoImpl();
        List<Employee> empList = employeeDao.empList();
        System.out.println(empList);
        for (Employee emp : empList) {
            System.out.println(emp.getName());
        }

        //创建员工对象 emp
        java.util.Date date = new java.util.Date();
        Employee emp = new Employee("00021", "小莉", "女", 20,
                "123456749712345676", "重庆", date);
        //调用添加方法
        int result = employeeDao.addEmployee(emp);
        System.out.println("是否添加成功：" + result);

        //创建修改员工的对象
        /*Employee emp1 = new Employee(17,"00020", "小莉", "女", 20,
                "123456749712345676", "重庆");
        int updResult = employeeDao.updateEmployee(emp1);
        System.out.println("是否修改成功：" + updResult);*/

        //调用删除员工的方法
        /*int delResult = employeeDao.deleteEmployee(20);
        System.out.println("是否删除成功：" + delResult);*/

    }

}
